Create Unpivot & Pivot Tables in Staging Database and Populate the Staging Tables 7

Click  and download the SQL files and Store Procedures.

Run Script that is attached to create your Staging tables in the Staging Database. Once ran the tables will be made up of these columns.












*Using SSIS Toolbox, the following components will be drag in the Control Flow Dashboard:



            1.     Drag and drop the Excel Source – Double click and add the excel connection manager and select the sheet from the excel to load the data



2.     Drag and drop the Conditional Split - Double click and add condition to remove the Null values 


3.     Drag and drop the Data Conversion – Select all dates and change the data type to Unicode string (255)

    

       4. Drag and drop the Unpivot transformation – double click and then selects the Data Conversion date column – in Destination column – Name the column as “QTY” and Pivot key value column name as “QuarterlyDate”


           5..     Drag and drop the Derived Column transformation – Add column “Filename” and add the source file name in the expression and make changes to “QuarterlyDate” column using this Code - 

RTRIM(LTRIM(SUBSTRING(QuarterlyDate,FINDSTRING(QuarterlyDate + "_","_",1) + 1,LEN(QuarterlyDate))))



6.    Drag and drop the Data Conversion and select QTY and change the data type to Unicode String (4000)


7.     Drag and drop the Destinationl Source – Double click and add the OLE- DB connection manager and select the destination table





Repeat the all the Steps for loading all the Business Property Type Source Files



The UnPivot & Pivot Staging tables are populated with the data from the Source Files




Merge all Pivot Tables into One Single Table

Drag and Drop the Data flow task and rename it - double click -

Drag and drop the OLE-DB Source file and double click and add source connection manager and select the source table

Drag and drop the derived column - use the Derived column Code 

Code used for Derived Column -

(DT_WSTR,8)REPLACE(REPLACE(Quarterlydate,"/",""),"/","")
Getdate()

Drag and drop the derived column - use the Derived column Code 

Code used for Derived Column1-

RIGHT("0" + QD,8)

Drag and drop the derived column - use the Derived column Code 

Code used for Derived Column2-

(SUBSTRING(QD,5,4) + "-" + SUBSTRING(QD,1,2) + "-" + SUBSTRING(QD,3,2))

Drag and drop the data conversion and make data conversion of the data to match the destination table

Drag and drop the OLE-DB destination Source and add destination connection manager and select the destination table



Repeat the Steps for rest of the business properties type 


Populated all Pivoted Tables in staging after excuting the package


Continued next step